import matplotlib.pyplot as plt
import dolphindb as ddb
from utilities.plot_correlation import plot_histograms

s = ddb.session("192.168.200.179", 8832, "chenzhimin", "suhhgjy98y_JHg87")


result = s.run("""

// 底部反弹
A=trans_for_doquery(get_stock_adjusting_front_price(1,`179600000000,1))

ods_stock_quotation = select secu_code as data_code, * from loadTable("dfs://ods_stock_quotation","ods_stock_quotation") where  if_trading_day = 1  
ods_stock_quotation =  select a.factor_value as close_price,  data_code, o.* from ej(ods_stock_quotation as o, A as a, `data_code`end_date)
riskstock_list=get_riskstock_list(st=true,delisting=true,suspend=true,lessthan90days=true,beijing=true)
stk_price = specialstock_list_filter(ods_stock_quotation, riskstock_list)
stk_price = select lead(close_price) as t1_close_price, lead(open_price) as t1_open_price, lead(low_price) as t1_low_price, lead(high_price) as t1_high_price, lag(turnover_volume) as tm1_turnover_volume, mavg(turnover_volume, 5) as m5d_turnover_volume, lead(turnover_volume) as t1_turnover_volume, lead(change_pct) as t1_change_pct, lag(change_pct) as tm1_change_pct, move(open_price, 1) as tm2_open_price, move(close_price, 1) as tm1_close_price, move(close_price, 2) as tm2_close_price, (move(close_price, -2)-close_price)\close_price as t0_t2_change_pct, (move(close_price, -3)-close_price)\close_price as t0_t3_change_pct, (move(close_price, -4)-close_price)\close_price as t0_t4_change_pct, (move(close_price, -5)-close_price)\close_price as t0_t5_change_pct, move(high_price, -2) as t2_high_price, lead(high_price)/close_price-1 as t1_high_return, move(close_price,5) as tm_5_close_price,  move(close_price, 2) as tm_2_close_price, move(close_price,3) as tm_3_close_price, move(close_price,20) as tm_20_close_price, move(close_price, -20) as t_20_close_price, move(close_price, -10) as t_10_close_price, move(close_price, -15) as t_15_close_price, move(close_price,-5) as t5_close_price,move(close_price,-6) as t6_close_price, end_date as trading_date, iif(lead(open_price) > close_price*1.01 and lead(high_price) >=1.02*close_price,1, 0) as next_high_open,  * from stk_price context by secu_code csort end_date
stk_price = select ratio(close_price, tm_5_close_price) as last_week_return, ratio(close_price, tm_3_close_price) as last_3days_return, ratio(close_price, tm_20_close_price) as last_month_return, ratio(t5_close_price, close_price) as next_week_return, ratio(t6_close_price, t1_close_price) as t1_next_week_return, ratio(t_10_close_price, close_price) as next_2week_return, ratio(t_15_close_price, close_price) as next_3week_return, ratio(t_20_close_price, close_price) as next_month_return, ratio(high_price, pre_close_price)-1 as high_return, * from stk_price

// 剔除科创、创业板
stk_deleted = select * from loadTable("dfs://XBBASE_listed_company_list", "listed_company_list") where  not(secu_abbr like '%ST%') and not(listed_sector in ('科创板', '创业板')) and not(secu_code like '%BJ')  

stk_price = select lag(last_week_return) as lag_last_week_return, * from ej(stk_price as s, stk_deleted as d, `secu_code) where open_price>=1 context by secu_code csort trading_date

 
tgt_bottom_stk1 = select 'bottom1' as stk_type, s.* 
from stk_price as s
// where ((change_pct >= 0.055 and turnover_volume >= m5d_turnover_volume) or change_pct >= 0.08)   and not(secu_code like '%BJ')  and open_price>=1 and not(high_price == low_price) context by secu_code csort trading_date
// where  (last_month_return <= 0.8 ) and not(secu_code like '%BJ')  and open_price>=1 and not(high_price == low_price) context by secu_code csort trading_date
where   not(secu_code like '%BJ')  and open_price>=1 and not(high_price == low_price) context by secu_code csort trading_date

// tgt_bottom_stk2 = select 'bottom2' as stk_type, s.* 
// from stk_price as s
// // where ((change_pct >= 0.055 and turnover_volume >= m5d_turnover_volume) or change_pct >= 0.08)   and not(secu_code like '%BJ')  and open_price>=1 and not(high_price == low_price) context by secu_code csort trading_date
// // where  (last_week_return <= 0.85 ) and not(secu_code like '%BJ')  and open_price>=1 and not(high_price == low_price) context by secu_code csort trading_date
// where  not(secu_code like '%BJ')  and open_price>=1 and not(high_price == low_price) context by secu_code csort trading_date



B=get_indicator_data_matrix(`1,`0,`6)
month_mainmoneyflow = select date(end_date) as trading_date, data_code as secu_code, round(factor_value, 2) as month_dde_turnover_value from loadTable("dfs://dwm_stock_factor_value_day", "dwm_stock_factor_value_day") where factor_id = `240400000001 order by end_date desc  
weekly_mainmoneyflow = select date(end_date) as trading_date, data_code as secu_code, round(factor_value, 2) as week_dde_turnover_value from trans_for_doquery(stock_main_inflow_ratio(B, 1))
daily_mainmoneyflow = select date(end_date) as trading_date, data_code as secu_code, round(factor_value, 2) as day_dde_turnover_value from trans_for_doquery(stock_main_inflow_ratio(B, 1))

tgt_stk = unionAll([tgt_bottom_stk1])

tgt_stk = select b.*, month_dde_turnover_value, lag(month_dde_turnover_value) as tm1_month_dde_turnover_value from lj(tgt_stk as b, month_mainmoneyflow as m, `trading_date`secu_code) 
tgt_stk = select b.*, week_dde_turnover_value, lag(week_dde_turnover_value) as tm1_week_dde_turnover_value from lj(tgt_stk as b, weekly_mainmoneyflow as m, `trading_date`secu_code) 
tgt_stk = select b.*, day_dde_turnover_value, lag(day_dde_turnover_value) as tm1_day_dde_turnover_value from lj(tgt_stk as b, daily_mainmoneyflow as m, `trading_date`secu_code) context by secu_code csort trading_date
tgt_stk = select * from tgt_stk where close_price > 2.5 and high_return >= 0.055 and not(high_price==low_price) and not(high_price==open_price)  and total_MV >= 2000000000 

select * from tgt_stk where trading_date >= 2014.01.01
""")

plot_histograms(result, ["day_dde_turnover_value"])

x = 1
